﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidades.Espectaculos;
using System.Configuration;
using System.Data.SqlClient;

namespace Datos.Espectaculos
{
    public class EspEspectaculoDAL
    {
        public static string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        public static List<EspEspectaculo> ListarEspectaculo(int idEspectaculo, DateTime fecha, int idLocal)
        {
           
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarEspectaculo]", con);
            com.Parameters.AddWithValue("@IdEspectaculo", (idEspectaculo == -1 ? (object)DBNull.Value : idEspectaculo));
            com.Parameters.AddWithValue("@Fecha", (fecha == DateTime.MinValue ? (object)DBNull.Value : fecha));
            com.Parameters.AddWithValue("@IdLocal", idLocal);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<EspEspectaculo> lista = new List<EspEspectaculo>();
                EspEspectaculo obj = null;
                while (dr.Read())
                {
                    obj = new EspEspectaculo();

                    obj.IdEspectaculo = Convert.ToInt32(dr["IdEspectaculo"].ToString());
                    obj.Nombre = dr["Nombre"].ToString();
                    obj.Descripcion = dr["Descripcion"].ToString();
                    obj.Fecha = Convert.ToDateTime(dr["Fecha"].ToString());
                    obj.IdLocal = Convert.ToInt32(dr["IdLocal"].ToString());
                    obj.IdEstado = Convert.ToInt32(dr["IdEstado"].ToString());
                    obj.Local = dr["Local"].ToString();
                    obj.Estado = dr["Estado"].ToString();

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public static EspEspectaculo ObtenerEspectaculo(int idEspectaculo)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarEspectaculo]", con);
            com.Parameters.AddWithValue("@IdEspectaculo", idEspectaculo);
            com.Parameters.AddWithValue("@Fecha", (object)DBNull.Value);
            com.Parameters.AddWithValue("@IdLocal", (object)DBNull.Value);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                //List<Espectaculo> lista = new List<Espectaculo>();
                EspEspectaculo obj = null;
                while (dr.Read())
                {
                    obj = new EspEspectaculo();

                    obj.IdEspectaculo = Convert.ToInt32(dr["IdEspectaculo"].ToString());
                    obj.Nombre = dr["Nombre"].ToString();
                    obj.Descripcion = dr["Descripcion"].ToString();
                    obj.Fecha = Convert.ToDateTime(dr["Fecha"].ToString());
                    obj.IdLocal = Convert.ToInt32(dr["IdLocal"].ToString());
                    obj.IdEstado = Convert.ToInt32(dr["IdEstado"].ToString());
                    obj.Local = dr["Local"].ToString();
                }
                dr.Close();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public static int AgregarEspectaculo(EspEspectaculo obj)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_AgregarEspectaculo]", con);

            com.Parameters.AddWithValue("@Nombre", obj.Nombre);
            com.Parameters.AddWithValue("@Descripcion", obj.Descripcion);
            com.Parameters.AddWithValue("@Fecha", obj.Fecha);
            com.Parameters.AddWithValue("@IdLocal", obj.IdLocal);
            com.Parameters.AddWithValue("@IdEstado", obj.IdEstado);

            SqlParameter p = new SqlParameter();
            p.Direction = System.Data.ParameterDirection.Output;
            p.Value = 0;
            p.ParameterName = "@newIdEspectaculo";

            com.Parameters.Add(p);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    result = Convert.ToInt32(com.Parameters["@newIdEspectaculo"].Value);
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public static int ModificarEspectaculo(EspEspectaculo obj)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ModificarEspectaculo]", con);

            com.Parameters.AddWithValue("@Nombre", obj.Nombre);
            com.Parameters.AddWithValue("@Descripcion", obj.Descripcion);
            com.Parameters.AddWithValue("@IdEstado", obj.IdEstado);
            com.Parameters.AddWithValue("@IdEspectaculo", obj.IdEspectaculo);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }
        }

        public static int EliminarEspectaculo(int IdEspectaculo)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_EliminarEspectaculo]", con);

            com.Parameters.AddWithValue("@IdEspectaculo", IdEspectaculo);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();

                int result = com.ExecuteNonQuery();
                //if (result > 0)
                //{
                //    com.Transaction.Commit();
                //}
                //else
                //{
                //    com.Transaction.Rollback();
                //}

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

        }


        public static int AgregarEspectaculoArtista(int Idespectaculo, int IdArtista, DateTime HoraInicio, int Duracion)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_AgregarEspectaculoArtista]", con);

            com.Parameters.AddWithValue("@Idespectaculo", Idespectaculo);
            com.Parameters.AddWithValue("@IdArtista", IdArtista);
            com.Parameters.AddWithValue("@HoraInicio", HoraInicio);
            com.Parameters.AddWithValue("@Duracion", Duracion);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }
        }

        public static int EliminarEspectaculoArtista(int Idespectaculo, int IdArtista)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_EliminarEspectaculoArtista]", con);

            com.Parameters.AddWithValue("@Idespectaculo", Idespectaculo);
            com.Parameters.AddWithValue("@IdArtista", IdArtista);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

        public static List<EspEspectaculoArtista> ListarEspectaculoArtista(int idEspectaculo)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarEspectaculoArtista]", con);
            com.Parameters.AddWithValue("@IdEspectaculo", idEspectaculo);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<EspEspectaculoArtista> lista = new List<EspEspectaculoArtista>();
                EspEspectaculoArtista obj = null;
                while (dr.Read())
                {
                    obj = new EspEspectaculoArtista();

                    obj.IdEspectaculo = Convert.ToInt32(dr["IdEspectaculo"].ToString());
                    obj.Nombre = dr["Nombre"].ToString();
                    obj.HoraInicio = Convert.ToDateTime(dr["HoraInicio"].ToString());
                    obj.IdArtista = Convert.ToInt32(dr["IdArtista"].ToString());
                    obj.Duracion = Convert.ToInt32(dr["Duracion"].ToString());
                    obj.SubTipo = dr["SubTipo"].ToString();

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }


        public static int AgregarEspectaculoRequisito(int Idespectaculo, string Descripcion, string Cantidad)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_AgregarEspectaculoRequisito]", con);

            com.Parameters.AddWithValue("@Descripcion", Descripcion);
            com.Parameters.AddWithValue("@Observacion", "");
            com.Parameters.AddWithValue("@Completado", 0);
            com.Parameters.AddWithValue("@IdEspectaculo", Idespectaculo);
            com.Parameters.AddWithValue("@Cantidad", Cantidad);
            SqlParameter p = new SqlParameter();
            p.Direction = System.Data.ParameterDirection.Output;
            p.Value = 0;
            p.ParameterName = "@newIdRequisito";

            com.Parameters.Add(p);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    result = Convert.ToInt32(com.Parameters["@newIdRequisito"].Value);
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }
        }

        public static int EliminarEspectaculoRequisito(int IdRequisito)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_EliminarEspectaculoRequisito]", con);

            com.Parameters.AddWithValue("@IdRequisito", IdRequisito);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                com.Transaction = con.BeginTransaction();
                int result = com.ExecuteNonQuery();
                if (result > 0)
                {
                    com.Transaction.Commit();
                }
                else
                {
                    com.Transaction.Rollback();
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }
        }

        public static List<EspEspectaculoRequisito> ListarEspectaculoRequisito(int idEspectaculo)
        {
            //string cnx = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(cnx);
            SqlCommand com = new SqlCommand("[dbo].[sp_Esp_ListarEspectaculoRequisito]", con);
            com.Parameters.AddWithValue("@IdEspectaculo", idEspectaculo);

            com.CommandType = System.Data.CommandType.StoredProcedure;
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                List<EspEspectaculoRequisito> lista = new List<EspEspectaculoRequisito>();
                EspEspectaculoRequisito obj = null;
                while (dr.Read())
                {
                    obj = new EspEspectaculoRequisito();
                    obj.IdRequisito = Convert.ToInt32(dr["IdRequisito"].ToString());
                    obj.IdEspectaculo = Convert.ToInt32(dr["IdEspectaculo"].ToString());
                    obj.Descripcion = dr["Descripcion"].ToString();
                    obj.Observacion = dr["Observacion"].ToString();
                    obj.Completado = Convert.ToInt32(dr["Completado"].ToString());
                    obj.Cantidad = dr["Cantidad"].ToString();

                    lista.Add(obj);
                }
                dr.Close();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();

            }

        }

    }
}
